﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Text.RegularExpressions;
using iTFF.WPF.CP;

namespace iTFF.WPF.SQLCE
{
    class DatabaseUtils
    {

        private string _connectionString;

        public DatabaseUtils()
        {
            // save the database in the library folder path
            _connectionString = ControlPanel.Instance.SQLCeDatabaseConnectionString;
        }

        #region Base methods

        /// <summary>
        /// Gets the current version from the AppInfo table
        /// </summary>
        /// <returns></returns>
        public Version GetCurrentVersion()
        {
            Version retVal = null;
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
                {

                    SqlCeCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT Value FROM AppInfo WHERE Property = 'Version'";
                    conn.Open();
                    retVal = new Version((string)cmd.ExecuteScalar());
                }
            }
            catch (Exception)
            {
                retVal = new Version("");
            }
            return retVal;
        }

        /// <summary>
        /// Executes a series of T-SQL commands seperated by GO statements
        /// </summary>
        /// <param name="strFile">passed update script</param>
        public bool RunScript(string script)
        {
            // Parse the update script into a set of executable commands
            string[] strCommands;
            strCommands = ParseScriptToCommands(script);
            SqlCeTransaction txn = null;
            using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
            {
                SqlCeCommand cmd = new SqlCeCommand();
                cmd.Connection = conn;
                conn.Open();
                txn = conn.BeginTransaction();
                foreach (string commandText in strCommands)
                {
                    try
                    {
                        if (commandText.Trim().Length > 0)
                        {
                            cmd.CommandText = commandText;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlCeException ex)
                    {
                        txn.Rollback();
                        throw new Exception(
                            string.Format("Unable to run Script:\\r\\n{0}\\r\\nFailed at\\r\\n{1}",
                            script,
                            commandText), ex);
                    }
                }
                return true;
            }
        }

        /// <summary>
        /// Parsing function for batch scripts
        /// </summary>
        /// <param name="strScript">script</param>
        private string[] ParseScriptToCommands(string strScript)
        {
            string[] commands = Regex.Split(strScript, "\r\nGO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }

        /// <summary>
        /// Creates an empty database based on the ConnectionString Info
        /// </summary>
        /// <remarks>
        /// </remarks>
        public void CreateDatabase()
        {
            CreateDatabase(string.Empty);
        }

        /// <summary>
        /// Creates the database from a script in the resources
        /// </summary>
        /// <remarks>
        /// Initializes the database by first creating the database file, creating the schemas, then loading initial data
        /// </remarks>
        public void CreateDatabase(string creationScripts)
        {
            using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
            {
                if (!System.IO.File.Exists(conn.Database))
                {
                    using (SqlCeEngine engine = new SqlCeEngine(_connectionString))
                    {
                        engine.CreateDatabase();
                    }
                }
            }
            if (creationScripts.Trim().Length > 0)
            {
                RunScript(creationScripts);
            }
        }

        /// <summary>
        /// Deletes the database file.
        /// For DEBUG purpose only
        /// </summary>
        public void DeleteDatabase()
        {

            using (SqlCeConnection conn = new SqlCeConnection())
            {
                conn.ConnectionString = _connectionString;
                if (System.IO.File.Exists(conn.Database))
                    System.IO.File.Delete(conn.Database);
            }
        }



        /// <summary>
        /// Verifies the database exists.  
        /// Assumes the database is file based, and parses the AttachDBFileName 
        ///     of the connection string to determine the physical file
        /// If the file doesn't exist, will create the User Instance file, 
        ///     create any default schema, load default data
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns>true if created, false if previously existed</returns>
        private bool VerifyDatabaseExists()
        {
            bool exists = false;
            using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
            {
                //  The SqlCeConnection.Database contains the file parth portion 
                //  of the database from the full connectionstring
                if (System.IO.File.Exists(conn.Database))
                {
                    //  No file, no database
                    exists = true;
                }
            }
            return exists;
        }

        /// <summary>
        /// CheckVersion() manages the database creation and versioning
        /// </summary>
        /// <remarks>If the database does not exist, CheckVersion() will install it
        /// from the resource file. Once the database exists, CheckVersion() will verify the version
        /// against the dbVersion property of the application and return a versionCheck enumeration.</remarks>
        /// <returns>versionCheck</returns>
        protected VersionCheck CheckVersion(Version expectedVersion)
        {
            // the database exists, so check it's version against
            // the application version.
            VersionCheck retVal = VersionCheck.Failed;

            Version v;
            try
            {
                // since the database file is present connect to it.
                using (SqlCeConnection conn = new SqlCeConnection(_connectionString))
                {
                    conn.Open();
                    SqlCeCommand cmd = new SqlCeCommand(
                        "SELECT Value from AppInfo WHERE Property = 'Version'",
                        conn);
                    v = new Version((string)cmd.ExecuteScalar());
                    // return a value from the enum
                    //#error Should only set AppInfo after an initial Sync
                    if (v == expectedVersion)
                        retVal = VersionCheck.Equal;
                    if (v > expectedVersion)
                        retVal = VersionCheck.NewerVersion;
                    if (v < expectedVersion)
                        retVal = VersionCheck.OlderVersion;
                }

            }
            catch (Exception)
            {
                retVal = VersionCheck.Failed;
            }
            // if it didn't exit yet, then something went wonky
            return retVal;
        }

        #endregion

        #region Case focused methods

        /// <summary>
        /// Verifies if the database is correct. Creates if it isn't yet.
        /// </summary>
        /// <returns></returns>
        public bool VerifyDatabase()
        {
            bool retVal = false;

            // Does the database even exist, if so, what version is it?
            if (!VerifyDatabaseExists())
            {
                // No database - need to create it
                CreateDatabase(Scripts.Scripts.CreateSchema);
                retVal = true;
            }
            else
            {
                var versionCheck = CheckVersion(new Version(Settings.Default.SQLCeDatabaseVersion));

                switch (versionCheck)
                {
                    case VersionCheck.Equal:
                        retVal = true;
                        break;

                    case VersionCheck.OlderVersion:
                        //TODO: Check for older database version
                        break;

                    case VersionCheck.Failed:
                        break;

                    case VersionCheck.NewerVersion:
                        retVal = false;
                        break;
                }
            }

            return retVal;
        }

        #endregion
    }
}
